package com.powerunion.datacollection.persistence.pagination.imp;

import java.sql.*;
import java.util.*;

import org.apache.log4j.Logger;

import com.powerunion.datacollection.persistence.db.ConnectionFactory;
import com.powerunion.datacollection.persistence.db.jdbc.operator.DBOperatorException;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.pagination.IPagination;
import com.powerunion.datacollection.persistence.pagination.PageHelper;
import com.powerunion.datacollection.persistence.pagination.PageParameter;
import com.powerunion.datacollection.persistence.pagination.PageResult;
import com.powerunion.datacollection.persistence.pagination.PaginationException;
import com.powerunion.datacollection.util.cache.ICache;
import com.powerunion.datacollection.util.cache.PeriodCache;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class OraclePaginationImp  implements IPagination {
  private static ICache countCache = new PeriodCache(120);
	private static final Logger logger = LogHelper.getInstance().getLogger(
			OraclePaginationImp.class);

  public OraclePaginationImp() {

  }
  private int getRecordAmount(Connection conn, PageParameter pInfo) throws
      DBOperatorException {
    Integer amount = null;
    if (pInfo.isCacheRecordAmountFlag()) {
      Integer key = new Integer(pInfo.getUserSql().hashCode());
      amount = (Integer) countCache.get(key);
      if (amount == null) {
        amount = caleAmount(conn, pInfo);
        countCache.put(key, amount);
      }
    }
    else {
      amount = caleAmount(conn, pInfo);
    }
    return amount.intValue();
  }

  private Integer caleAmount(Connection conn, PageParameter pInfo) throws
      NumberFormatException, DBOperatorException {
    // select count(1) from (select * from account) t
    Integer amount = null;
    QueryOperator query = new QueryOperator();
    query.setUseOnlyConnectionFlag(true);
    if (!pInfo.getSqlParameters().isEmpty()) {
      for (int i = 0; i < pInfo.getSqlParameters().size(); i++) {
        SqlParameter sp = (SqlParameter) pInfo.getSqlParameters().get(i);
        query.addParameter(sp);
      }
    }
    query.setSql("select count(1) from (" + pInfo.getUserSql() + ") c_t");
    query.setPresentConnection(conn);
    query.access();
    RsDataSet rs = new RsDataSet(query.getSqlResultSet());
    amount = new Integer(rs.getFieldValueAsString(0));
    rs.clearAll();
    if (logger.isDebugEnabled()) {
      logger.debug("amount:" + amount.intValue());
    }
    return amount;
  }

  public PageResult page(PageParameter pInfo) throws
      PaginationException {
    Connection conn = null;
    PageResult pr = new PageResult();
    try {
      conn = ConnectionFactory.getInstance().getConncetion();
      int pos = pInfo.getPageNumber() - 1;
      if (pos >= 0) {
        QueryOperator query = new QueryOperator();
        query.setUseOnlyConnectionFlag(true);
        query.setPresentConnection(conn);
        String usersql = pInfo.getUserSql();
        StringBuffer sb = new StringBuffer();
        sb.append("select * from ( select row_.*, rownum rownum_ from ( ");
        if (usersql.indexOf("?") > 0) {
          StringTokenizer st = new StringTokenizer(usersql, "?");
          int i = 0;
          while (st.hasMoreTokens()) {
            sb.append(st.nextToken());
            if (i >= pInfo.getSqlParameters().size()) {
              break;
            }
            SqlParameter sp = (SqlParameter) pInfo.getSqlParameters().get(i);
            int type = sp.getType();
            if (type == SqlType.INTEGER || type == SqlType.BIGINT ||
                type == SqlType.DECIMAL || type == SqlType.DOUBLE ||
                type == SqlType.FLOAT || type == SqlType.NUMERIC ||
                type == SqlType.REAL || type == SqlType.SMALLINT) {
              if (sp.getValue() != null) {
                sb.append(sp.getValue().toString());
              }
              else {
                sb.append("null");
              }
            }
            else {
              if (sp.getValue() != null) {
                sb.append("'");
                sb.append(sp.getValue().toString());
                sb.append("'");
              }
              else {
                sb.append("null");
              }
            }
            i++;
          }
        }
        else {
          sb.append(usersql);
        }
        int pn = pInfo.getPageNumber();
        int bn = ( (pn - 1) * pInfo.getPageSize());
        int en = bn + pInfo.getPageSize();
        //sb.append(" rows " + bn + " to " + en);
        sb.append(" ) row_ where rownum <= " + en + " ) where rownum_ > " + bn);
        String usql = sb.toString();
        if (logger.isDebugEnabled()) {
          logger.debug("sql:" + usql);
        }
        query.setSql(usql);
        query.access();
        if (query.resultSetAvailable()) {
          pr.setSqlResultSet(query.getSqlResultSet());
          pr.setCurPageNumber(pInfo.getPageNumber() + 1);
          pr.setCurPageSize(pr.getSqlResultSet().size());
        }
      }
      pr.setRecordAmount(getRecordAmount(conn, pInfo));
      PageHelper.countPageInfo(pInfo, pr);
      if (logger.isDebugEnabled()) {
        logger.debug("==Page Info===");
        logger.debug("curPageNumber:" + pr.getCurPageNumber());
        logger.debug("nextPageNumber:" + pr.getNextPageNumber());
        logger.debug("prePageNumber:" + pr.getPrePageNumber());
        logger.debug("pageAmount:" + pr.getPageAmount());
        logger.debug("curPos:" + pr.getCurPos());
        logger.debug("curPageSize:" + pr.getCurPageSize());
        logger.debug("recordAmount:" + pr.getRecordAmount());
      }

    }
    catch (Exception e) {
      e.printStackTrace();
      logger.error(e);
      throw new PaginationException(e);
    }
    finally {
      DBUtils.close(conn);
      if (!pInfo.getSqlParameters().isEmpty()) {
        pInfo.getSqlParameters().clear();
      }
      return pr;
    }
  }

}
